import random
import pymysql
from flask import Flask, redirect, jsonify
from utils import get_mysql_connection
from flask_cors import CORS
app = Flask(__name__)
CORS(app)


@app.route('/')
def show_index():
    return redirect('/static/index.html')

@app.route('/api/general_data')
def get_generate_data():
    conn = get_mysql_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute('select sum(orderAmount) from tb_order')
            gmv = round(float(cursor.fetchone()[0]) / 10000, 2)
            cursor.execute('select sum(payment) sales from tb_order')
            sales = round(float(cursor.fetchone()[0]) / 10000, 2)
            cursor.execute('select sum(payment) real_sales from tb_order where chargeback="NO"')
            real_sales = round(float(cursor.fetchone()[0]) / 10000, 2)
            cursor.execute('select sum(payment) / count(distinct userID) from tb_order where chargeback="NO"')
            arppu = round(float(cursor.fetchone()[0]), 2)
    except pymysql.MySQLError as err:
        print(err)
    finally:
        conn.close()
    return {'gmv': gmv, 'sales': sales, 'real_sales': real_sales, 'arppu': arppu}

@app.route('/api/gmv_trend')
def get_gmv_trend():
    conn = get_mysql_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute('''
                SELECT 
                    DATE_FORMAT(orderTime, '%m') as month,
                    ROUND(SUM(orderAmount)/10000, 2) as gmv
                FROM tb_order
                WHERE orderTime IS NOT NULL
                GROUP BY month
                ORDER BY month
            ''')
            result = cursor.fetchall()
            print('GMV趋势查询结果:', result)
            
            dates = [f"{row[0]}月" for row in result]
            values = [float(row[1]) for row in result]
            return {'dates': dates, 'values': values}
            
    except pymysql.MySQLError as err:
        print('GMV趋势查询错误:', err)
        return {'dates': [], 'values': []}
    finally:
        conn.close()

@app.route('/api/channel_distribution')
def get_channel_distribution():
    conn = get_mysql_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute('''
                SELECT 
                    COALESCE(chanelID, '未知') as channel,
                    COUNT(DISTINCT userID) as user_count
                FROM tb_order
                GROUP BY chanelID
                ORDER BY user_count DESC
            ''')
            result = cursor.fetchall()
            print('渠道分布查询结果:', result)
            
            channels = [row[0] for row in result]
            values = [int(row[1]) for row in result]
            return {'channels': channels, 'values': values}
            
    except pymysql.MySQLError as err:
        print('渠道分布查询错误:', err)
        return {'channels': [], 'values': []}
    finally:
        conn.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0', debug=True)
